package com.example.myapplication.dao;
import android.util.Log;

import com.example.myapplication.model.FieldNotebook;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

public class MySQLConnection {
    private static final String URL = "jdbc:mysql://47.110.225.218:3306/Android";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";
    private Connection conn;



    // 连接数据库
    public boolean connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");//
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 断开数据库连接
    public void disconnect() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public boolean isNameExists(String name) {
        if (!isConnected()) {
            connect();
            if (!isConnected()) return false;
        }
        String sql = "SELECT COUNT(*) FROM FieldNotebook WHERE name = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
              int i=rs.getInt(1) ;
              return i>0;
            }
        } catch (SQLException e) {
            Log.e("MySQLConnection", "检查 name 失败: " + e.getMessage());
            e.printStackTrace();
        }
        return false;
    }

    public  boolean isRecordMorethan2(String name) {
        if (!isConnected()) {
            connect();
            if (!isConnected()) return false;
        }
        String sql = "SELECT COUNT(*) FROM FieldNotebook WHERE name = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                int i=rs.getInt(1) ;
                return i>=2;
            }
        } catch (SQLException e) {
            Log.e("MySQLConnection", "检查记录数量失败: " + e.getMessage());
            e.printStackTrace();
        }
        return false;
    }
    // 增 - 插入数据
    public boolean insert(int pointId,String name ,Date recordTime, double longitude, double latitude,
                          String outcrop, String pointMeaning, String description, byte[] photo) {
        if (!isConnected()) {
            connect();
            if (!isConnected()) return false;
        }


        String sql = "INSERT INTO FieldNotebook (point_id,name ,record_time, longitude, latitude, outcrop, point_meaning, description, photo) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, pointId);
            pstmt.setString(2, name);
            pstmt.setTimestamp(3, new java.sql.Timestamp(recordTime.getTime()));
            pstmt.setDouble(4, longitude);
            pstmt.setDouble(5, latitude);
            pstmt.setString(6, outcrop);
            pstmt.setString(7, pointMeaning);
            pstmt.setString(8, description);
            pstmt.setBytes(9, photo);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }



    public boolean update(int pointId,String name ,Date recordTime, double longitude, double latitude,
                          String outcrop, String pointMeaning, String description, byte[] photo) {
        if (!isConnected()) {
            connect();
            if (!isConnected()) return false;
        }


        String sql = "UPDATE FieldNotebook SET point_id = ?, record_time = ?, longitude = ?, latitude = ?, outcrop = ?, point_meaning = ?, description = ?, photo = ? WHERE name = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, pointId);
            pstmt.setTimestamp(2, new java.sql.Timestamp(recordTime.getTime()));
            pstmt.setDouble(3, longitude);
            pstmt.setDouble(4, latitude);
            pstmt.setString(5, outcrop);
            pstmt.setString(6, pointMeaning);
            pstmt.setString(7, description);
            pstmt.setBytes(8, photo);
            pstmt.setString(9, name); // 设置更新条件的 name
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    public FieldNotebook getRecordByname(String name) {
        if (!isConnected()) {
            connect();
            if (!isConnected()) return null;
        }
        String sql = "SELECT * FROM FieldNotebook WHERE name = ?";//我这个只查一条
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                FieldNotebook record = new FieldNotebook();
                record.pointId = rs.getInt("point_id");
                record.name = rs.getString("name");
                record.recordTime = new Date(rs.getTimestamp("record_time").getTime());
                record.longitude = rs.getDouble("longitude");
                record.latitude = rs.getDouble("latitude");
                record.outcrop = rs.getString("outcrop");
                record.pointMeaning = rs.getString("point_meaning");
                record.description = rs.getString("description");
                record.photo = rs.getBytes("photo");
                return record;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<FieldNotebook> getAllRecords() {
        List<FieldNotebook> records = new ArrayList<>();
        if (!isConnected()) {
            connect();
            if (!isConnected()) return records;
        }
        String sql = "SELECT * FROM FieldNotebook";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                FieldNotebook record = new FieldNotebook();
                record.pointId = rs.getInt("point_id");
                record.name = rs.getString("name");
                record.recordTime = rs.getTimestamp("record_time") != null ? new Date(rs.getTimestamp("record_time").getTime()) : null;
                record.longitude = rs.getDouble("longitude");
                record.latitude = rs.getDouble("latitude");
                record.outcrop = rs.getString("outcrop");
                record.pointMeaning = rs.getString("point_meaning");
                record.description = rs.getString("description");
                record.photo = rs.getBytes("photo");
                records.add(record);
            }
        } catch (SQLException e) {
            Log.e("MySQLConnection", "查询所有记录失败: " + e.getMessage());
            e.printStackTrace();
        }
        return records;
    }

    // 删 - 删除数据
    public boolean delete(String name) {
        String sql = "DELETE FROM FieldNotebook WHERE name = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 改 - 更新数据
//    public boolean update(int id, String name, int age) {
//        String sql = "UPDATE Test SET name = ?, age = ? WHERE id = ?";
//        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
//            pstmt.setString(1, name);
//            pstmt.setInt(2, age);
//            pstmt.setInt(3, id);
//            return pstmt.executeUpdate() > 0;
//        } catch (SQLException e) {
//            e.printStackTrace();
//            return false;
//        }
//    }

    // 查 - 查询所有数据
//    public List<String> queryAll() {
//        List<String> results = new ArrayList<>();
//        String sql = "SELECT * FROM Test";
//        try (PreparedStatement pstmt = conn.prepareStatement(sql);
//             ResultSet rs = pstmt.executeQuery()) {
//            while (rs.next()) {
//                double id = rs.getInt("id");
//                String name = rs.getString("name");
//                double age = rs.getInt("age");
//                results.add( "名称: " + name +"，经度: " + id + ", 维度: " + age+",描述   ");
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return results;
//    }

    // 检查连接状态
    public boolean isConnected() {
        try {
            return conn != null && !conn.isClosed();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
}